package com.axinfu.util.config.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.axinfu.util.DateUtil;
import com.axinfu.util.config.BtgSet;
import com.axinfu.util.config.BtgSetPlugin;
import com.axinfu.util.config.Dialect;
import com.axinfu.util.serial.Identities;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.UUID;

/**
 * BtgSetHelper
 *
 * @author zjn
 * @since 2022/3/24
 */
public class BtgSetHelper {

    private static final String STR_SQL = "sql";
    private static final String STR_MODULE = "module";
    private static final String STR_PARAM = "param";
    private static final String STR_BTG_SET_CFG_BAK = "btg_set_cfg_bak";

    private static final BtgSetPlugin BTG_SET_PLUGIN = BtgSetPlugin.getBtgSetPlugin();
    private static final BtgSetHelper BTG_SET_HELPER = new BtgSetHelper();

    private static JSONObject bakJson = new JSONObject();

    public static BtgSetHelper getInstance() {
        return BTG_SET_HELPER;
    }

    private int indexNo = 0;

    private BtgSetHelper() {
    }

    public JSONObject getResetSql(Dialect dialect) {
        String xml;
        if (dialect == Dialect.ORACLE) {
            xml = "oracle.xml";
        } else {
            xml = "mysql.xml";
        }
        JSONObject re = new JSONObject();
        try {
            InputStream in = this.getClass().getResourceAsStream("/config/sql/" + xml);
            SAXReader reader = new SAXReader();
            Document document;
            Element root;
            document = reader.read(in);
            root = document.getRootElement();
            for (Iterator<?> i = root.elementIterator(STR_SQL); i.hasNext(); ) {
                Element sql = (Element) i.next();
                String table = sql.attributeValue("table");
                String value = sql.getStringValue();
                re.put(table, value);
            }
        } catch (DocumentException e) {
            e.printStackTrace();
        }
        return re;
    }

    /**
     * 取得数据库表名
     *
     * @param dialect dialect
     * @return List
     */
    public List<String> getTabName(Dialect dialect) {
        String xml;
        if (dialect == Dialect.ORACLE) {
            xml = "oracle.xml";
        } else {
            xml = "mysql.xml";
        }
        List<String> list = new ArrayList<>();
        try {
            InputStream in = this.getClass().getResourceAsStream("/config/sql/" + xml);
            SAXReader reader = new SAXReader();
            Document document;
            Element root;
            document = reader.read(in);
            root = document.getRootElement();
            for (Iterator<?> i = root.elementIterator(STR_SQL); i.hasNext(); ) {
                Element sql = (Element) i.next();
                String table = sql.attributeValue("table");
                list.add(table);
            }
        } catch (DocumentException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 读取配置文件
     *
     * @return JSONArray
     */
    public JSONArray readDefault() {
        JSONArray arr = new JSONArray();
        if (BTG_SET_PLUGIN.getTemplet() == null) {
            throw new RuntimeException("文件模板参数【setTemplet】未设置值!");
        }
        try {
            InputStream in = this.getClass().getResourceAsStream("/" + BTG_SET_PLUGIN.getTemplet());

            System.out.println("读取sql初始化模板文件开始!");
            SAXReader reader = new SAXReader();
            Document document;
            Element root;
            document = reader.read(in);
            root = document.getRootElement();

            //重置排序号
            indexNo = 0;

            arr.addAll(getParam(root, true));
            for (Iterator<?> i = root.elementIterator(STR_MODULE); i.hasNext(); ) {
                Element module = (Element) i.next();
                arr.addAll(getParam(module, false));
            }
            System.out.println("读取sql初始化模板文件结束!");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return arr;
    }

    private JSONArray getParam(Element el, boolean isRoot) {
        JSONArray arr = new JSONArray();
        String msg;
        for (Iterator<?> j = el.elementIterator(STR_PARAM); j.hasNext(); ) {
            Element param = (Element) j.next();
            String code = param.attributeValue("code");
            if (code == null || "".equals(code)) {
                msg = BTG_SET_PLUGIN.getTemplet() + " 节点未配置属性【code】" + param;
                throw new RuntimeException(msg);
            }
            JSONObject json = new JSONObject();
            String type = param.attributeValue("type");
            if (type == null || "".equals(type)) {
                type = "String";
            }
            String name = param.attributeValue("name") != null ? param.attributeValue("name") : "";
            String desc = param.attributeValue("desc") != null ? param.attributeValue("desc") : "";
            String value = param.getTextTrim();

            json.put("code", code.trim());
            json.put("name", name.trim());
            json.put("type", type.trim());
            json.put("desc", desc.trim());
            json.put("value", value);
            json.put("group", isRoot ? "默认" : el.attributeValue("name").trim());
            json.put("indexNo", indexNo++);

            arr.add(json);
        }
        return arr;
    }

    /**
     * 重置数据库环境
     */
    @SuppressWarnings("all")
    public void resetDataBaseEnv() {
        Connection conn;
        Statement statement = null;
        ResultSet rs;
        try {
            conn = BTG_SET_PLUGIN.getConnection();
            JSONObject re = getResetSql(BTG_SET_PLUGIN.getDialect());
            statement = conn.createStatement();

            try {
                //备份备份表数据
                if (re.containsKey(STR_BTG_SET_CFG_BAK)) {
                    String sql = "select * from " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_bak");
                    rs = statement.executeQuery(sql);

                    JSONObject obj = new JSONObject();
                    while (rs.next()) {
                        obj.put("key_id", rs.getString("key_id"));
                        obj.put("i_time", rs.getString("i_time"));
                        obj.put("content", rs.getString("content"));
                        break;
                    }
                    bakJson = obj;
                }
            } catch (Exception e) {
                System.out.println("查询备份表数据失败！");
            }

            //特殊处理oracle删除已存在配置表
            if (BTG_SET_PLUGIN.getDialect().equals(Dialect.ORACLE)) {
                for (String s : BTG_SET_PLUGIN.getTabMap().values()) {
                    String str = "DECLARE\n" +
                            "  NUM NUMBER;\n" +
                            "BEGIN\n" +
                            "  SELECT COUNT(1)\n" +
                            "    INTO NUM\n" +
                            "    FROM USER_TABLES\n" +
                            "   WHERE TABLE_NAME = '" + s.toUpperCase() + "';\n" +
                            "  IF NUM = 1 THEN\n" +
                            "    EXECUTE IMMEDIATE 'drop table " + s.toUpperCase() + "';\n" +
                            "  END IF;\n" +
                            "END;\n";
                    statement.execute(str);
                }
            }

            for (String s : re.keySet()) {
                String tab = BTG_SET_PLUGIN.getTabMap().get(s);
                String[] sql = re.getString(s).split(";");
                for (String str : sql) {
                    if (str.trim().length() > 0) {
                        statement.execute(str.replaceAll("(?i)" + s, tab));
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 将默认配置写入数据库
     */
    @SuppressWarnings("all")
    public void writeDefaultSet() {
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement pstms = null;
        try {
            JSONArray arr = readDefault();
            conn = BTG_SET_PLUGIN.getConnection();
            stmt = conn.createStatement();
            ResultSet rs;
            String sql;

            workUser(conn);

            String defaultCode = "default";
            String defaultName = "默认配置";

            String keyId = workModule(conn, stmt, defaultCode, defaultName);

            //删除默认模板的值
            sql = "delete from " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_miv") + " where module_id='" + keyId + "'";
            stmt.execute(sql);

            for (int i = 0; i < arr.size(); i++) {
                JSONObject json = arr.getJSONObject(i);
                sql = "select * from " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_item") + " where item_code=?";
                pstms = conn.prepareStatement(sql);
                pstms.setString(1, json.getString("name"));
                rs = pstms.executeQuery();
                String itemId = null;
                while (rs.next()) {
                    itemId = rs.getString("key_id");
                    break;
                }
                if (itemId == null) {
                    itemId = UUID.randomUUID().toString().replaceAll("-", "");
                    sql = "insert into " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_item") + "(key_id,item_code," +
                            "item_name,item_desc,data_type,group_name,index_no)";
                    sql += "values(?,?,?,?,?,?,?)";
                    pstms = conn.prepareStatement(sql);
                    pstms.setString(1, itemId);
                    pstms.setString(2, json.getString("code"));
                    pstms.setString(3, json.getString("name"));
                    pstms.setString(4, json.getString("desc"));
                    pstms.setString(5, json.getString("type"));
                    pstms.setString(6, json.getString("group"));
                    pstms.setInt(7, json.getInteger("indexNo"));
                    pstms.execute();
                }

                sql = "insert into " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_miv") + "(key_id,module_id," +
                        "module_code,item_code,item_value,remark,i_time)";
                sql += "values(?,?,?,?,?,?,?)";

                String mivId = UUID.randomUUID().toString().replaceAll("-", "");
                pstms = conn.prepareStatement(sql);
                pstms.setString(1, mivId);
                pstms.setString(2, keyId);
                pstms.setString(3, defaultCode);
                pstms.setString(4, json.getString("code"));
                pstms.setString(5, json.getString("value"));
                pstms.setString(6, json.getString("desc"));
                pstms.setTimestamp(7, new Timestamp(DateUtil.getNow().getTime()));
                pstms.execute();
            }
            if (bakJson.keySet().size() > 0) {
                sql = "insert into " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_bak") + "(key_id,i_time,content)" +
                        "values(?,?,?)";
                pstms = conn.prepareStatement(sql);
                pstms.setString(1, bakJson.getString("key_id"));
                pstms.setTimestamp(2, new Timestamp(DateUtil.parseTimestamp(bakJson.getString("i_time")).getTime()));
                pstms.setString(3, bakJson.getString("content"));
                pstms.execute();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BtgSetUtils.closeDb(conn, stmt, pstms, null);
        }
    }

    /**
     * workModule
     *
     * @param conn        conn
     * @param stmt        stmt
     * @param defaultCode defaultCode
     * @param defaultName defaultName
     * @return String
     * @throws SQLException SQLException
     */
    @SuppressWarnings("all")
    private String workModule(Connection conn, Statement stmt, String defaultCode, String defaultName) throws SQLException {
        //判断是否存在默认配置
        String sql = "select * from " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_module")
                + " where set_code='" + defaultCode + "'";

        String keyId = null;
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            keyId = rs.getString("key_id");
            break;
        }
        if (keyId != null) {
            sql = "update " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_module") + " set set_name='" + defaultName + "',state=1 where key_id='" + keyId + "'";
            stmt.execute(sql);
        } else {
            keyId = UUID.randomUUID().toString().replaceAll("-", "");
            sql = "insert into " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_module") + "(key_id,set_code," +
                    "set_name,set_desc,state)";
            sql += "values(?,?,?,?,1)";
            PreparedStatement pstms = conn.prepareStatement(sql);
            pstms.setString(1, keyId);
            pstms.setString(2, defaultCode);
            pstms.setString(3, defaultName);
            pstms.setString(4, defaultName);
            pstms.execute();
            pstms.clearParameters();
            pstms.close();
        }
        return keyId;
    }

    /**
     * workUser
     *
     * @param conn conn
     * @throws SQLException SQLException
     */
    @SuppressWarnings("all")
    private void workUser(Connection conn) throws SQLException {
        String loginId = "admin";
        String loginPwd = "admin123!@#";

        //写入默认用户名和密码
        String sql = "INSERT INTO  " + BTG_SET_PLUGIN.getTabMap().get("btg_set_user") + " (key_id, login_id, " +
                "login_pwd, uname, last_time)" +
                " VALUES (?,?,?,?,?)";

        PreparedStatement pstms = conn.prepareStatement(sql);
        pstms.setString(1, Identities.uuid());
        pstms.setString(2, loginId);
        pstms.setString(3, loginPwd);
        pstms.setString(4, "管理员");
        pstms.setTimestamp(5, new Timestamp(DateUtil.getNow().getTime()));
        pstms.execute();
    }

    /**
     * initValue
     *
     * @return JSONObject
     */
    @SuppressWarnings("all")
    public JSONObject initValue() {
        JSONObject json = new JSONObject();
        String sql = "select * from " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_module") + " where state=1";
        Connection conn = BTG_SET_PLUGIN.getConnection();
        Statement statement = null;
        ResultSet rs = null;
        try {
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            String moduleId = null;
            while (rs.next()) {
                moduleId = rs.getString("key_id");
                break;
            }
            json = initValue(moduleId);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BtgSetUtils.closeDb(conn, statement, null, rs);
        }
        return json;
    }

    /**
     * initValue
     *
     * @param moduleId moduleId
     * @return JSONObject
     */
    @SuppressWarnings("all")
    public JSONObject initValue(String moduleId) {
        JSONObject json = new JSONObject();
        Connection conn = BTG_SET_PLUGIN.getConnection();
        Statement statement = null;
        ResultSet rs = null;
        String sql = "select t.*,(select i.data_type from " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_item")
                + " " + "i " +
                "where t.item_code=i.item_code)data_type from " + BTG_SET_PLUGIN.getTabMap().get("btg_set_cfg_miv") +
                " t  where t.module_id='" + moduleId + "'";
        try {
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            String itemCode = null, itemValue = null, dataType = null;
            while (rs.next()) {
                itemCode = rs.getString("item_code");
                itemValue = rs.getString("item_value");
                dataType = rs.getString("data_type");
                json.put(itemCode, itemValue);
            }
            BtgSet.init(json);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BtgSetUtils.closeDb(conn, statement, null, rs);
        }
        return json;
    }
}
